﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.Linq;
using System.Linq;
using wordroid.utils;

namespace wordroid.Model
{
    public class DBHelper
    {
        private DBHelper() {
            _context = new WordroidDataContext();
            initDb();
        }

        public static DBHelper getInstance() {
            return INSTANCE ?? (INSTANCE = new DBHelper());
        }

        public int? isWordExists(String originalWord) {
            foreach (Word word in Words) {
                if(word.originalWord.Equals(originalWord)) {
                    return word.ID;
                }
            }

            return null;
        }

        public void removeWord(Word word) {
            
            IQueryable<Word> queryable = from c in _context.words where c.ID == word.ID select c;
            Word forDelete = queryable.FirstOrDefault();
            
            if(forDelete != null) {
                IQueryable<Translation> queryableTrans = from t in _context.translations where t.Word.ID == word.ID select t;

                _context.translations.DeleteAllOnSubmit(queryableTrans);
                _context.words.DeleteOnSubmit(forDelete);
                _context.SubmitChanges();
                _words.Remove(forDelete);
            }
        }

        public List<Translation>  getTranslations(Word word) {
            IQueryable<Translation> queryableTrans = from t in _context.translations where t.Word.ID == word.ID select t;
            return new List<Translation>(queryableTrans);
        }

        public void SaveWordAsNew(String originalWord, List<String> trans, List<String> invTrans) {

            if(String.IsNullOrWhiteSpace(originalWord)) {
                throw new WordIsEmptyException();
            }

            IQueryable<Word> queryableWord = from w in _context.words where w.originalWord.Equals(originalWord) select w;

            if (queryableWord.Any()) {
                throw new WordExistsException();
            }
            
            createWord(originalWord, trans, invTrans);
        }

        public void EditWord(int? wordId, String originalWord, List<String> translations, List<String> invalidTranslations) {

            if (wordId == null) {
                SaveWordAsNew(originalWord, translations, invalidTranslations);
                return;
            }
            
            if (String.IsNullOrWhiteSpace(originalWord)) {
                throw new WordIsEmptyException();
            }

            if (!translations.Any() || !invalidTranslations.Any()) {
                throw new TranslationsIsEmptyException();
            }

            IQueryable<Word> queryableWord = from w in _context.words where w.originalWord.Equals(originalWord) select w;

            if (queryableWord.Count() > 1 && queryableWord.First().ID != wordId) {
                throw new WordExistsException();
            }

            Word word = null;
            foreach (Word w in Words) {
                if (w.ID == wordId) {
                    word = w;
                    break;
                }
            }

            if(word == null) {
                SaveWordAsNew(originalWord, translations, invalidTranslations);
                return;
            }

            word.originalWord = originalWord;
            IQueryable<Translation> queryableTrans = from t in _context.translations where t.Word.ID == wordId select t;
            _context.GetTable<Translation>().DeleteAllOnSubmit(queryableTrans);

            insertTranslations(translations, true, word);
            insertTranslations(invalidTranslations, false, word);

            _context.SubmitChanges();
        }

        private void initDb() {
            
            if (!_context.DatabaseExists()) {
                _context.CreateDatabase();
                initData();
            }
        }

        public ObservableCollection<Word> Words {
            get {
                if(_words == null) {
                    _words = new ObservableCollection<Word>();

                    Table<Word> table = _context.GetTable<Word>();

                    foreach (var word in table) {
                        _words.Add(word);
                    }
                }

                return _words;
            }
        }

        public Word getWordById(int id) {
            foreach (Word word in Words) {
                if(word.ID == id) {
                    return word;
                }
            }

            return null;
        }

        #region words creation
        private void createWord(String originalName, List<String> translations, List<String> invalidTranslations) {

            if(!translations.Any() || !invalidTranslations.Any()) {
                throw new TranslationsIsEmptyException();
            }

            Word word = new Word();
            word.originalWord = originalName;
            _context.words.InsertOnSubmit(word);

            insertTranslations(translations, true, word);
            insertTranslations(invalidTranslations, false, word);

            _context.SubmitChanges();
            Words.Add(word);
        }

        private void insertTranslations(List<String> translations, Boolean isValid, Word word) {

            foreach (var translation in translations) {

                if (translation != null) {
                    Translation t = new Translation();
                    t.isValid = isValid;
                    t.translation = translation;
                    t.Word = word;

                    _context.translations.InsertOnSubmit(t);
                }
            }
        }

        private void initData() {
            createWord("Яблоко", new List<string> { "Pomum", "Apple" },
                    new List<string> { "Mackintosh", "Hackintosh" });

            createWord("Апельсин", new List<string> { "Orange" },
                new List<string> { "Apelsin", "Sunball", "Juice" });

            createWord("Груша", new List<string> { "Pear", "Punching Bag", "Punchbag" },
                new List<string> { "Pineapple", "Punch" });

            createWord("Мандарин", new List<string> { "Tangerine", "Mandarine" },
                new List<string> { "Orange", "Mardanin" });

            createWord("Кокос", new List<string> { "Coco", "Cocoanut", "Cokernut" },
                new List<string> { "Wood egg", "Bounty" });

            createWord("Ананас", new List<string> { "Ananas", "Pine" },
                new List<string> { "Painapple", "Bushfrut" });

            createWord("Банан", new List<string> { "Banana" },
                new List<string> { "Cucumber", "Savannah" });

            createWord("Грейпфрут", new List<string> { "Grapefrut", "Pomelo" },
                new List<string> { "Orangefrut", "Pamella Anderson" });
        }

        #endregion

        private static DBHelper INSTANCE;        
        private readonly WordroidDataContext _context;
        private ObservableCollection<Word> _words;
    }
}